package dao.impl;

import dao.ComscoreDao;
import pojo.Comscore;
import pojo.FontMessage;
import util.BaseDao;
import util.PageBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 商品积分表的dao层接口实现类
 * @author 代传斌
 */
public class ComscoreDaoImpl implements ComscoreDao {

    /*全局对象*/
//    PageBean pageBean = new PageBean();//获取分页对象

    Connection connection = null;//获取连接对象
    PreparedStatement statement = null;//获取发送sql的statement对象
    ResultSet resultSet = null;//获得结果集对象

    /**
     * 查询商品积分条数，分页所用
     * @return  result 返回查询到的信息条数
     */
    public int getDateCount() {
        int result = 0;
        StringBuffer buffer = new StringBuffer();
        buffer.append("SELECT COUNT(R) ROWCOUNT FROM(SELECT ROWNUM R,A.*,B.GO_NAME FROM COMSCORE A INNER JOIN GOODS B ON A.WARE_ID=B.GO_ID)");
        //若消息状态为已读（state=1）或未读（state=0），则根据消息状态查询,否则查询所有信息的条数

        try {
            connection= BaseDao.getConnection();
            statement = connection.prepareStatement(buffer.toString());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                result = resultSet.getInt("ROWCOUNT");//根据别名获得查询结果（信息条数）
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(resultSet,statement,connection);
        }
        return result;
    }
    /**
     * 分页查询商品积分信息
     */
    @Override
    public PageBean ComscoreList(PageBean pageBean) {
        List<Comscore> list =new ArrayList<Comscore>();
        pageBean.setTotalCount(this.getDateCount());//为分页信息类pageBean添加数据总数
        if (pageBean.getPageSize()==0){
            pageBean.setPageSize(1);
        }//防止设置总页数pages时除数为零
        //pages:总页数,若每页数据条数能被数据总量整除，则总页数为两者整除的结果，否则为结果+1
        int pages=this.getDateCount()%pageBean.getPageSize()==0 ? this.getDateCount()/pageBean.getPageSize():this.getDateCount()/pageBean.getPageSize();
        pageBean.setTotalPage(this.getDateCount());//设置总页数
        pageBean.setTotalPage(pages);
        int beginRow = (pageBean.getPageIndex() - 1) * pageBean.getPageSize() + 1;
        int endRow = pageBean.getPageIndex() * pageBean.getPageSize();
        try {
            connection= BaseDao.getConnection();
            String sql="SELECT * FROM(SELECT ROWNUM R,A.*,B.GO_NAME NAME FROM COMSCORE A INNER JOIN GOODS B ON A.WARE_ID=B.GO_ID)  WHERE R BETWEEN " +beginRow+
                    " AND "+endRow;
            statement=connection.prepareStatement(sql);
            resultSet=statement.executeQuery();
            Comscore cs=null;
            while (resultSet.next()){
               cs=new Comscore();
                cs.setCeId(resultSet.getInt("ce_id"));
                cs.setWareId(resultSet.getInt("ware_id"));
                cs.setPointId(resultSet.getInt("ce_pointid"));
                cs.setGoodName(resultSet.getString("NAME"));
                list.add(cs);
            }
            System.out.println("名称"+list.get(0).getGoodName());;
            pageBean.setList(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pageBean;
    }

}
